<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * Covers all database manipulation for ajax methods
 *
 * @package     VisualQ Local System
 * @author      CSU OJT Students '10 - '11 (Michael Roland B. Martin)
 * @copyright   Copyright (c) 2010 - 2011, Bitstop Network Services, Inc.
 * @version     1.0 January 27, 2011
 */

class Ajax_model extends Model {

    function Ajax_model()
    {
        parent::Model();

        $this->CI =& get_instance();
    }

    /**
     * Get one finished logs
     *
     * Used by:
     *      ajax/send_logs/
     *
     * @return  object
     *      query   :   the query result
     */
    function get_unsent_log()
    {
        // Get the unsent logs services
        $this->db->select('logs.id, agent_id, timestart, timeend, processing_time, issue_time, queue_number, mobile_number, closing_code, service_id as client_service_id');
        $this->db->join('accounts', 'accounts.id = logs.account_id');
        $this->db->where('closing_code IS NOT NULL', NULL);
        $this->db->where('submit', 0);
        $this->db->limit(1);
        $query = $this->db->get('logs');
        $row = $query->row_array();

        return $query;
    }

    /**
     * Set sent log as sent
     *
     * Used by:
     *      ajax/send_logs/
     *
     * @params  array
     *      log id     :   log id of the log sent
     */
    function set_logs_sent($li)
    {
        // Set sent log as sent
        $this->db->where('id', $li);
        $this->db->update('logs', array('submit' => 1));
    }

    /**
     * Generate a new queue number
     *
     * Used by:
     *      ajax/new_queue_number/
     *
     * @param	array
     *      si              : service id
     *      mn              : mobile number
     * @return  string
     *      queue_number    :   the generated queue number, format SSNNNN where SS is the prefix and NNNN is the number
     */
    function new_queue_number($params)
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Get the service details
        $this->db->where('id', $params['si']);
        $query = $this->db->get('services');
        $row = $query->row_array();

        // Get the service prefix
        $prefix = $row['prefix'];

        // Checks if the last number exceeds 9999
        if($row['last_number'] < 9999)
        {
            $queue_number = $row['last_number'] + 1;
        }
        else
        {
            $queue_number = 1;
        }

        // Change the last issued number
        $this->db->where('id', $params['si']);
        $this->db->update('services', array('last_number' => $queue_number));

        // Data to be added
        $new_log = array(
           'issue_time'     => now(),
           'queue_number'   => $queue_number,
           'mobile_number'  => $params['mn'],
           'service_id'     => $params['si']
        );

        // Insert the data to the database
        $this->db->insert('logs', $new_log);

        return sprintf('%s%04d', $prefix, $queue_number);
    }

    /**
     * Get a queue number
     *
     * Used by:
     *      ajax/get_queue_number/
     *
     * @param	bool
     *      active          :   0 - get an inactive and unassigned queue number, 1 - get the active queue number assigned to the agent
     * @return  object
     *      query           :   the query result
     */
    function get_queue_number($active)
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Get the services assigned to the agent
        $this->db->where('account_id', $this->session->userdata('id'));
        $query = $this->db->get('accounts_services');

        foreach($query->result_array() as $row)
        {
            $services[] = $row['service_id'];
        }

        // Set where default values
        $where = array(
            'account_id'    => NULL
        );

        if($active) {
            // Overwrite where values
            $where['account_id'] = $this->session->userdata('id');
            $where['active'] = 1;
        }

        // Look for the earliest queue number
        $this->db->select('`logs`.`id` as `log_id`, `call_counter`, `services`.`id` as `service_id`, `services`.`title` as `service_title`, `services`.`prefix` as `service_prefix`, `timestart`, `issue_time`, `queue_number`');
        $this->db->join('services', 'logs.service_id = services.id');
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $this->db->where_in('service_id', $services);
        $this->db->order_by('issue_time');
        $this->db->limit(1);

        return $this->db->get('logs');
    }

    /**
     * Associates the agent to the queue number and sets the queue number to active
     *
     * Used by:
     *      ajax/get_queue_number/
     *
     * @param	int
     *      li              :   log id
     */
    function set_active($li)
    {
        $update = array(
            'account_id'    => $this->session->userdata('id'),
            'active'        => 1,
            'unit_name'     => $this->session->userdata('unit_name')
        );

        // Build query
        $this->db->where('`id`', $li);
        $this->db->update('logs', $update);
    }

    /**
     * Set a missed number to active
     *
     * Used by:
     *      ajax/serve_missed/
     *
     * @param	int
     *      li              :   log id
     */
    function serve_missed($li)
    {
        $update = array(
            'active'        => 1
        );

        // Build query
        $this->db->where('`id`', $li);
        $this->db->update('logs', $update);
    }

    /**
     * Gets the row containing the log id and mobile number combination
     *
     * Used by:
     *      ajax/confirm_mobile_number/
     *
     * @param	array
     *      li              :   log id
     *      mn              :   mobile number
     * @return  object
     *      query           :   the query result
     */
    function confirm_mobile_number($params)
    {
        // Where condition
        $where = array(
            'id'            => $params['li'],
            'mobile_number' => $params['mn']
        );

        // Build query
        $this->db->where($where);
        return $this->db->get('logs');
    }

    /**
     * Sets a time start for the queue number
     *
     * Used by:
     *      ajax/confirm_mobile_number/
     *
     * @param	int
     *      li              :   log id
     */
    function start_service($li)
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Set the time start
        $this->db->where('id', $li);
        $this->db->update('logs', array('timestart' => now(), 'call_counter' => 1));
    }

    /**
     * Increments the call counter of the queue number, removes active status
     *
     * Used by:
     *      ajax/skip_queue_number/
     *
     * @param	int
     *      li              :   log id
     */
    function skip_queue_number($li)
    {
        // Set update data
        $update = array(
            'active'        => 0,
            'call_counter'  => 1
        );

        // Increment the call counter and remove active status
        $this->db->where('id', $li);
        $this->db->update('logs', $update);
    }

    /**
     * Sets the time end, total processing time and closing code of the queue number, removes active status
     *
     * Used by:
     *      ajax/end_service/
     *
     * @param	array
     *      li              :   log id
     *      cc              :   closing code
     * @return  array
     *      si              :   service id
     *      pt              :   processing time
     */
    function end_service($params)
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Get the queue number details
        $this->db->where('id', $params['li']);
        $query = $this->db->get('logs');

        if($query->num_rows() > 0)
        {
            $row = $query->row_array();

            // Set update data
            $update = array(
                'timeend'           => now(),
                'processing_time'   => now() - $row['timestart'],
                'closing_code'      => $params['cc'],
                'active'            => 0
            );

            // Update queue number data
            $this->db->where('id', $row['id']);
            $this->db->update('logs', $update);
        }

        $return['si'] = $row['service_id'];
        $return['pt'] = $update['processing_time'];

        return $return;
    }

    /**
     * Get the latest queue number of a service
     *
     * Used by:
     *      ajax/get_latest_number/
     *
     * @param	int
     *      si              :   service id
     * @return  object
     *      query           :   the query result
     */
    function get_latest_number($si)
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Where conditions
        $where = array(
            'account_id IS NOT NULL'    => NULL,
            'unit_name IS NOT NULL'     => NULL,
            'service_id'                => $si,
            'call_counter'              => 0
        );

        // Build query
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $this->db->order_by('id', 'DESC');

        return $this->db->get('logs');
    }

    /**
     * Get the agents data for the flexigrid
     *
     * NOTE: The method is based on the codeigniter implementation prototype
     *
     * Used by:
     *      ajax/agents_grid/
     *
     * @return  array
     *      records         :   all agent data
     *      record_count    :   number of records found
     */
    function agents_grid()
    {
        // Build query
        $this->db->select('id, last_name, first_name');
        $this->db->where('account_type', 'agent');
        $this->CI->flexigrid->build_query();

        // Get records
        $return['records'] = $this->db->get('accounts');

        // Build count query
        $this->db->select('count(id) as record_count');
        $this->db->where('account_type', 'agent');
        $this->CI->flexigrid->build_query(FALSE);
        $record_count = $this->db->get('accounts');
        $row = $record_count->row();

        // Get record count
        $return['record_count'] = $row->record_count;

        return $return;
    }

    /**
     * Get the logs data for the flexigrid
     *
     * NOTE: The method is based on the codeigniter implementation prototype
     *
     * Used by:
     *      ajax/logs_grid/
     *
     * @params  string
     *      type            :   what type of logs to be shown (waiting, served or missed)
     * @return  array
     *      records         :   all agent data
     *      record_count    :   number of records found
     */
    function logs_grid($type = 'waiting')
    {
        // If type is 'waiting', get all the active agent's services
        if(strcmp($type, 'waiting') == 0)
        {
            $query = $this->db->get_where('accounts_services', array('account_id' => $this->session->userdata('id')));
            foreach($query->result_array() as $row)
            {
                $sid[] = $row['service_id'];
            }
        }

        // Build select clause
        $select = '`logs`.`id`, `services`.`title`, `issue_time`, `queue_number`';
        if(strcmp($type, 'served') == 0)
        {
            $select .= ', `timestart`, `timeend`, `processing_time`, `closing_code`';
        }

        // Build query
        $this->db->select($select);
        $this->db->join('services', 'logs.service_id = services.id');
        // If type is 'served' or 'missed', consider the active agent
        if(strcmp($type, 'served') == 0 OR strcmp($type, 'missed') == 0)
        {
            if(strcmp($type, 'missed') == 0)
            {
                // Where condition if type is 'missed'
                $where = array(
                    'closing_code'              => NULL,
                    'call_counter !='           => 0,
                    'active'                    => 0
                );
            }
            else
            {
                // Where condition if type is 'served'
                $where['closing_code IS NOT NULL'] = NULL;
            }
            $where['account_id'] = $this->session->userdata['id'];
            $this->db->where($where);
        }
        else
        {
            // Where condition if type is 'waiting'
            $this->db->where('account_id', NULL);
            $this->db->where_in('service_id', $sid);
        }
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $this->CI->flexigrid->build_query();

        // Get records
        $return['records'] = $this->db->get('logs');

        // Build count query
        $this->db->select('count(logs.id) as record_count');
        $this->CI->flexigrid->build_query(FALSE);
        $record_count = $this->db->get('logs');
        $row = $record_count->row();

        //Get Record Count
        $return['record_count'] = $row->record_count;

        return $return;
    }

    /**
     * Get the latest agent statistics
     *
     * Used by:
     *      ajax/get_agent_stats/
     *
     * @return  array
     *      waiting     :   number waiting in queue
     *      served      :   number of queue numbers served and finished
     *      missed      :   number of queue numbers missed
     */
    function get_agent_stats()
    {
        // Load necessary library, helper and model files
        $this->load->helper('date');

        // Get the agents services
        $query = $this->db->get_where('accounts_services', array('account_id' => $this->session->userdata('id')));
        foreach($query->result_array() as $row)
        {
            $sid[] = $row['service_id'];
        }

        // Build query for the 'waiting' statistics
        $this->db->where('account_id', NULL);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $this->db->where_in('service_id', $sid);

        // Get records and record count
        $query = $this->db->get('logs');
        $num['waiting'] = $query->num_rows();

        // Where conditions for 'served' statistics
        $where = array(
            'account_id'                => $this->session->userdata('id'),
            'closing_code IS NOT NULL'  => NULL
        );

        // Build query for the 'served' statistics
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $query = $this->db->get('logs');
        $num['served'] = $query->num_rows();

        // Where conditions for the 'missed' statistics
        $where = array(
            'account_id'                => $this->session->userdata('id'),
            'closing_code IS NULL'      => NULL,
            'call_counter !='           => 0,
            'active'                    => 0
        );

        // Build query for the 'missed' statistics
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $query = $this->db->get('logs');
        $num['missed'] = $query->num_rows();

        return $num;
    }

}

/* End of file ajax_model.php */
/* Location: ./system/application/model/ajax_model.php */